/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.com.sparknet.jsindustrymanagerinput;

import cn.com.sparknet.jsindustrymanagerinput.ui.FLFGTreeNode;
import cn.com.sparknet.jsindustrymanagerinput.ui.XCSCTreeNode;
import com.wendell.database.Database;
import com.wendell.util.DateUtilities;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Administrator
 */
public final class DBManager {
    
    private DBManager()
    {
    }
    
    private static Database openDatabase() throws SQLException
    {
        Connection conn = null;
        try
        {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:JSIndustryManagerInput.db");
            return new Database(conn);
        }catch(ClassNotFoundException e)
        {
            if(conn != null)
            {
                conn.close();
            }
            throw new SQLException(e);
        }
    }
    
    public static void initDatabase() throws SQLException
    {
        Database db = null;
        ResultSet rs = null;
        try
        {
            db = openDatabase();
            String sql = "select 1 from sqlite_master where type='table' and name='t_flfg'";
            rs = db.getRS(sql);
            if(!rs.next())
            {
                sql = "create table t_flfg(id Integer primary key autoincrement,parent_id Integer,title varchar (200),content varchar (4000),order_id Integer,create_time varchar(20))";
                db.execSqlUpdate(sql);
            }
            sql = "select 1 from sqlite_master where type='table' and name='t_xcsc'";
            db.closeRS(rs);
            rs = db.getRS(sql);
            if(!rs.next())
            {
                sql = "create table t_xcsc(id Integer primary key autoincrement,parent_id Integer,title varchar (200),content varchar (4000),order_id Integer,create_time varchar(20))";
                db.execSqlUpdate(sql);
            }
        }finally
        {
            try
            {
                if(rs != null)
                {
                    db.closeRS(rs);
                }
            }finally
            {
                if(db != null)
                {
                    db.closeME();
                }
            }
        }
    }
    
    public static List<FLFGTreeNode> queryFLFGNodes(String parentId) throws SQLException
    {
        Database db = null;
        ResultSet rs = null;
        try
        {
            db = openDatabase();
            String sql = null;
            if(parentId.equals("-1"))
                sql = "select id,title from t_flfg where parent_id is null order by order_id,create_time";
            else
                sql = "select id,title from t_flfg where parent_id = " + parentId + " order by order_id,create_time";
            rs = db.getRS(sql);
            List<FLFGTreeNode> nodes = new ArrayList<FLFGTreeNode>();
            while(rs.next())
            {
                nodes.add(new FLFGTreeNode(rs.getString("id"),rs.getString("title")));
            }
            return nodes;
        }finally
        {
            try
            {
                if(rs != null)
                {
                    db.closeRS(rs);
                }
            }finally
            {
                if(db != null)
                {
                    db.closeME();
                }
            }
        }
    }
    
    public static List<XCSCTreeNode> queryXCSCNodes(String parentId) throws SQLException
    {
        Database db = null;
        ResultSet rs = null;
        try
        {
            db = openDatabase();
            String sql = null;
            if(parentId.equals("-1"))
                sql = "select id,title from t_xcsc where parent_id is null order by order_id,create_time";
            else
                sql = "select id,title from t_xcsc where parent_id = " + parentId + " order by order_id,create_time";
            rs = db.getRS(sql);
            List<XCSCTreeNode> nodes = new ArrayList<XCSCTreeNode>();
            while(rs.next())
            {
                nodes.add(new XCSCTreeNode(rs.getString("id"),rs.getString("title")));
            }
            return nodes;
        }finally
        {
            try
            {
                if(rs != null)
                {
                    db.closeRS(rs);
                }
            }finally
            {
                if(db != null)
                {
                    db.closeME();
                }
            }
        }
    }
    
    public static String[] queryFLFGDetail(String id) throws SQLException
    {
        Database db = null;
        ResultSet rs = null;
        try
        {
            db = openDatabase();
            String sql = "select content,order_id from t_flfg where id = " + id;
            rs = db.getRS(sql);
            if(!rs.next()) return null;
            return new String[]{rs.getString("content"),rs.getString("order_id")};
        }finally
        {
            try
            {
                if(rs != null)
                {
                    db.closeRS(rs);
                }
            }finally
            {
                if(db != null)
                {
                    db.closeME();
                }
            }
        }
    }
    
    public static String[] queryXCSCDetail(String id) throws SQLException
    {
        Database db = null;
        ResultSet rs = null;
        try
        {
            db = openDatabase();
            String sql = "select content,order_id from t_xcsc where id = " + id;
            rs = db.getRS(sql);
            if(!rs.next()) return null;
            return new String[]{rs.getString("content"),rs.getString("order_id")};
        }finally
        {
            try
            {
                if(rs != null)
                {
                    db.closeRS(rs);
                }
            }finally
            {
                if(db != null)
                {
                    db.closeME();
                }
            }
        }
    }
    
    public static void addFLFGInfo(String parentId,String title,String content,String orderId) throws SQLException
    {
        Database db = null;
        try
        {
            db = openDatabase();
            String sql = "insert into t_flfg(parent_id,title,content,order_id,create_time) values (?,?,?,?,?)";
            db.execPrepareSqlUpdate(sql, new String[]{parentId.equals("-1")?null:parentId,title,content,orderId,DateUtilities.getFormatDate("yyyy-MM-dd HH:mm:ss")});
        }finally
        {
            if(db != null)
            {
                db.closeME();
            }
        }
    }
    
    public static void addXCSCInfo(String parentId,String title,String content,String orderId) throws SQLException
    {
        Database db = null;
        try
        {
            db = openDatabase();
            String sql = "insert into t_xcsc(parent_id,title,content,order_id,create_time) values (?,?,?,?,?)";
            db.execPrepareSqlUpdate(sql, new String[]{parentId.equals("-1")?null:parentId,title,content,orderId,DateUtilities.getFormatDate("yyyy-MM-dd HH:mm:ss")});
        }finally
        {
            if(db != null)
            {
                db.closeME();
            }
        }
    }
    
    public static void updateFLFGInfo(String id,String title,String content,String orderId) throws SQLException
    {
        Database db = null;
        try
        {
            db = openDatabase();
            String sql = "update t_flfg set title = ?,content = ?,order_id = ? where id = ?";
            db.execPrepareSqlUpdate(sql, new String[]{title,content,orderId,id});
        }finally
        {
            if(db != null)
            {
                db.closeME();
            }
        }
    }
    
    public static void updateXCSCInfo(String id,String title,String content,String orderId) throws SQLException
    {
        Database db = null;
        try
        {
            db = openDatabase();
            String sql = "update t_xcsc set title = ?,content = ?,order_id = ? where id = ?";
            db.execPrepareSqlUpdate(sql, new String[]{title,content,orderId,id});
        }finally
        {
            if(db != null)
            {
                db.closeME();
            }
        }
    }
    
    public static void deleteFLFGInfo(String id) throws SQLException
    {
        Database db = null;
        try
        {
            db = openDatabase();
            db.setAutoCommit(false);
            deleteFLFGInfoImpl(db,id);
            db.commit();
        }catch(SQLException e)
        {
            if(db != null) db.rollback();
            throw e;
        }finally
        {
            if(db != null)
            {
                db.closeME();
            }
        }
    }
    
    private static void deleteFLFGInfoImpl(Database db,String id) throws SQLException
    {
        String sql = "delete from t_flfg where id = ?";
        db.execPrepareSqlUpdate(sql, new String[]{id});
        sql = "select id from t_flfg where parent_id = ?";
        ResultSet rs = null;
        try
        {
            rs = db.getPRS(sql, new String[]{id});
            while(rs.next())
            {
                deleteFLFGInfoImpl(db,rs.getString("id"));
            }
        }finally
        {
            if(rs != null)
                db.closeRS(rs);
        }
    }
    
    public static void deleteXCSCInfo(String id) throws SQLException
    {
        Database db = null;
        try
        {
            db = openDatabase();
            db.setAutoCommit(false);
            deleteXCSCInfoImpl(db,id);
            db.commit();
        }catch(SQLException e)
        {
            if(db != null) db.rollback();
            throw e;
        }finally
        {
            if(db != null)
            {
                db.closeME();
            }
        }
    }
    
    private static void deleteXCSCInfoImpl(Database db,String id) throws SQLException
    {
        String sql = "delete from t_xcsc where id = ?";
        db.execPrepareSqlUpdate(sql, new String[]{id});
        sql = "select id from t_xcsc where parent_id = ?";
        ResultSet rs = null;
        try
        {
            rs = db.getPRS(sql, new String[]{id});
            while(rs.next())
            {
                deleteXCSCInfoImpl(db,rs.getString("id"));
            }
        }finally
        {
            if(rs != null)
                db.closeRS(rs);
        }
    }
    
}
